package com.fsnbigdata.repository;

import com.fsnbigdata.domain.Log;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @Author:
 * @Date: Created in 19:01 2018/9/29
 * @Modified By:
 */
public interface LogRepository extends JpaRepository<Log,Long> {

    Page<Log> findAll(Specification<Log> sp, Pageable pageable);

    @Query(value = "select z from Log z where type=?1 ")
    List<Log> findAllByType(String type);

    @Query(value = "select * from log where if(?1!='',type=?1,1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from log where if(?1!='',type=?1,1=1)",
            nativeQuery = true)
    List<Log> queryList(String type, Pageable pageable);

    @Query(value = "select count(*) from log where if(?1!='',type=?1,1=1)", nativeQuery = true)
    Long getCount(String type);

    /**
     * 统计登录次数
     *
     * @param type    类型
     * @param name    用户名
     * @param oldTime 一周前的时间戳
     * @param nowTime 当前的时间戳
     * @return
     */
    @Query(value = "SELECT COUNT(*) FROM log " +
            "WHERE type=?1 " +
            "AND username <=> ?2 " +
            "AND UNIX_TIMESTAMP(create_time)>?3 " +
            "AND UNIX_TIMESTAMP(create_time)<=?4 ", nativeQuery = true)
    Long getCount(String type, String name, long oldTime, long nowTime);

    @Query(value = "SELECT * FROM log WHERE UNIX_TIMESTAMP(create_time)>=?1 " +
            "AND UNIX_TIMESTAMP(create_time)<=?2 ", nativeQuery = true)
    List<Log> findAllByTime(long startTime, long endTime);

    @Query(value = "SELECT * FROM log WHERE UNIX_TIMESTAMP(create_time)<=?1 " +
            "AND UNIX_TIMESTAMP(create_time)>?2 AND genre=?3", nativeQuery = true)
    List<Log> findAllByBeforeTime(long nowTime, long beforeTime, String genre);

    @Query(value = "SELECT * FROM log WHERE UNIX_TIMESTAMP(create_time)>=?1 " +
            "AND UNIX_TIMESTAMP(create_time)<=?2 " +
            "AND if(?3!='',genre=?3,1=1) " +
            "AND if(?4!='',description like concat('%',?4,'%'),1=1) ", nativeQuery = true)
    List<Log> findAllByTime(long startTime, long endTime, String type, String key);

    @Query(value = "SELECT * FROM log WHERE if(?1!='',genre=?1,1=1) " +
            "AND if(?2!='',description like concat('%',?2,'%'),1=1) ", nativeQuery = true)
    List<Log> findAllByTypeAndKey(String type, String key);

    @Query(value = "SELECT COUNT(id) FROM log WHERE UNIX_TIMESTAMP(create_time)>=?1 " +
            "AND UNIX_TIMESTAMP(create_time)<=?2 " +
            "AND if(?3!='',genre=?3,1=1) " +
            "AND if(?4!='',description like concat('%',?4,'%'),1=1) " +
            "AND if(?5!='',username=?5,1=1) ", nativeQuery = true)
    Integer queryCount(long startTime, long endTime, String type, String key,String username);

    @Query(value = "SELECT * FROM log ORDER BY create_time asc limit 1",nativeQuery = true)
    Log queryAllLimitOne();
}
